//
//  DataBase.m
//  Lesson_UI_17_Sqlite
//
//  Created by dlios on 15/8/25.
//  Copyright (c) 2015年 lanou3g.com 蓝鸥科技. All rights reserved.
//

#import "DataBase.h"
#import "MKBookModel.h"
@implementation DataBase

+(DataBase *)shareDataBase
{
    static DataBase *dbHandle = nil;
    if (dbHandle == nil) {
        dbHandle = [[DataBase alloc] init];
    }
    return dbHandle;
}

// 先创建一个全局的 数据库对象
static sqlite3 *db;

#pragma mark - 打开数据库
-(void)openDB
{
    // 获取沙盒路径
    NSString *documentPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject;

    //拼接sqlite文件路径
    NSString *sqlitePath = [documentPath stringByAppendingPathComponent:@"comic_id.sqlite"];
    
    NSLog(@"路径 = %@",sqlitePath);
    
    if (db != nil) {
        NSLog(@"数据库已经打开");
        return;
    }
    
    // 打开数据库的方法
    int reuslt = sqlite3_open(sqlitePath.UTF8String, &db);
    
    if (reuslt == SQLITE_OK) {
        NSLog(@"数据库打开成功");
    }else{
        NSLog(@"数据库打开失败");
    }
    
}

#pragma mark - 创建数据库表
-(void)createTable
{
    // 生成创建表的sql语句
    NSString *createSql = @"CREATE TABLE IF NOT EXISTS huodong(number INTEGER PRIMARY KEY AUTOINCREMENT, comic_id TEXT, page TEXT, cover_url TEXT, lastup TEXT,title TEXT)";
    
    // 执行sql语句
    int result = sqlite3_exec(db, createSql.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else{
        NSLog(@"创建表失败");
    }
    
    
}

#pragma mark - 插入信息(增)
- (void)insertBook:(MKBookModel *)model
{
    // 创建插入学生信息的sql语句
    NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO huodong(comic_id, page,cover_url,lastup,title) VALUES('%@','%@','%@','%@','%@')",model.comic_id,model.page,model.cover_url,model.lastup,model.title];
    
    int result = sqlite3_exec(db, insertSql.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"插入信息成功");
    }else{
        NSLog(@"插入信息失败");
    }
    
}

#pragma mark - 更新信息(改)
- (void)updateBook:(MKBookModel *)model number:(NSInteger)number
{
    // 创建更新学生信息的sql语句
    NSString *updateSql = [NSString stringWithFormat:@"UPDATE huodong SET comic_id = '%@',page = '%@',cover_url = '%@',lastup = '%@',title = '%@'  WHERE number = '%ld'",model.comic_id,model.page,model.cover_url,model.lastup,model.title, number];
    
    // 执行sql语句
    int result = sqlite3_exec(db, updateSql.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"更新成功");
    }else{
        NSLog(@"更新失败");
    }
    
}

#pragma mark - 删除信息(删)
- (void)deleteBook:(NSString *)title
{
    // 创建删除学生信息sql语句
    NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM huodong WHERE title = '%@'",title];
    
    // 执行sql语句
    int result = sqlite3_exec(db, deleteSql.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
}

#pragma mark - 查询信息(查)
- (NSMutableArray *)selectAll
{
    // 创建查询学生信息的sql语句
    NSString *selectSql = @"SELECT * FROM huodong";
    
    // 创建数据库跟随指针, 用来循环遍历数据库表的每行数据
    sqlite3_stmt *stmt = nil;
    
    // 执行语句
    // 参数1:数据库对象  参数2:查询语句  参数3:查询语句字数限制(-1为不限制)
    // 参数4:跟随的指针对象 参数5:nil
    int result = sqlite3_prepare_v2(db, selectSql.UTF8String, -1, &stmt, nil);
    
    // 创建存放信息的大数组
    NSMutableArray *array = [NSMutableArray array];
    if (result == SQLITE_OK) {
        //NSLog(@"查询准备工作完成");
    
    
    // 开始循环遍历查询数据库每一条数据
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        // 当跟随指针指向的行的数据符合你的查询条件, 逐一取出每列数据
        int number = sqlite3_column_int(stmt, 0);
        //const 修饰 就是 name不可被改变
        const unsigned char *comic_id = sqlite3_column_text(stmt, 1);
        const unsigned char *page = sqlite3_column_text(stmt, 2);
        const unsigned char *cover_url = sqlite3_column_text(stmt, 3);
        const unsigned char *lastup = sqlite3_column_text(stmt, 4);
        const unsigned char *title = sqlite3_column_text(stmt, 5);
        
        NSString *comic_idString = [NSString stringWithUTF8String:(const char *)comic_id];
        NSString *pageString = [NSString stringWithUTF8String:(const char *)page];
        NSString *cover_urlString = [NSString stringWithUTF8String:(const char *)cover_url];
        NSString *lastupString = [NSString stringWithUTF8String:(const char *)lastup];
        NSString *titleString = [NSString stringWithUTF8String:(const char *)title];

        MKBookModel *model = [[MKBookModel alloc] init];
        model.comic_id = comic_idString;
        model.page = pageString;
        model.cover_url = cover_urlString;
        model.lastup = lastupString;
        model.title = titleString;
        model.number = number;
        
        // 将学生对象存入大数组中
        [array addObject:model];
        
    }
        
    }else{
        NSLog(@"查询失败");
    }
    
    return array;
    
}

#pragma mark - 关闭数据库
-(void)closeDB
{
    db = nil;
    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {
        NSLog(@"关闭数据库成功");
    }else{
        NSLog(@"关闭数据库失败");
    }
}


#pragma mark - 删除数据库表
- (void)dropTable
{
    
    NSString *dropSql = @"DROP TABLE huodong";
    
    int result = sqlite3_exec(db, dropSql.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除表成功");
    }else{
        NSLog(@"删除表失败");
    }
}








@end
